﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Linq;
using CNative.Utilities;

namespace CNative.Dapper.Utils
{
    internal class SqlServer2000Provider : BaseProvider
    {
        public SqlServer2000Provider(IDbHelper _db) : base(_db)
        {
        }

        #region 关键字
        /// <summary>
        /// 数据库提供程序名字
        /// </summary>
        public override string ProviderName
        {
            get
            {
                return "System.Data.SqlClient";
            }
        }
        public override string ProviderNameFactory
        {
            get
            {
                return "System.Data.SqlClient.SqlClientFactory";
            }
        }
        /// <summary>
        /// 数据库类型
        /// </summary>
        public override DatabaseType DBType { get { return DatabaseType.SqlServer; } }
        /// <summary>
        /// 参数关键字 @ or :
        /// </summary>
        public override string ParamKeyword
        {
            get
            {
                return "@";
            }
        }
        /// <summary>
        /// 关键字前缀 [
        /// </summary>
        public override string SuffixLeft
        {
            get
            {
                return "[";
            }
        }
        /// <summary>
        /// 关键字后缀 ]
        /// </summary>
        public override string SuffixRigh
        {
            get
            {
                return "]";
            }
        }
        #endregion

        #region DbProviderFactory
        public override string GetSchemaTableName(Type tableType)
        {
            var tb = Funs.GetDbTableInfo(dbHelper, tableType);
            if (tb == null)
            {
                return "";
            }
            return tb.Schema.IsNullOrEmpty() ? SuffixLeft + tb.TableName + SuffixRigh : SuffixLeft + tb.Schema + SuffixRigh + ".dbo." + SuffixLeft + tb.TableName + SuffixRigh;
        }
        #endregion

        #region GetDbParameter
        ///// <summary>
        ///// 获取DbParameter
        ///// </summary>
        ///// <param name="dbType">数据库类型</param>
        ///// <returns></returns>
        //public override IDataParameter GetDbParameter(string propName, object val, string paramSuffix = "exp_",
        //    DbTableInfo tb = null, ParameterDirection direction = ParameterDirection.Input, DbType valDBType = DbType.String)
        //{
        //    IDataParameter para = null;
        //    //--------------------------------------------------------------------------------------------------------------
        //    var frets = FillerParameter(propName, val, paramSuffix);
        //    if (frets.Item1)
        //    {
        //        return para;
        //    }
        //    var parameterName = frets.Item2;
        //    val = frets.Item3;
        //    //--------------------------------------------------------------------------------------------------------------
        //    #region SqlServer
        //    if (direction == ParameterDirection.Output)
        //    {
        //        para = new System.Data.SqlClient.SqlParameter()
        //        {
        //            ParameterName = parameterName,
        //            Value = val,
        //            DbType = valDBType,
        //            Direction = direction
        //        };
        //        return para;
        //    }
        //    para = new System.Data.SqlClient.SqlParameter(parameterName, val) {  Direction= direction };
        //    if (para is System.Data.SqlClient.SqlParameter par)
        //    {
        //        if (tb != null && tb.TableInfo != null)
        //        {
        //            #region TableInfo

        //            var dtinfo = tb.TableInfo.Find(f =>
        //                f.Name.Equals(propName.Trim(), StringComparison.OrdinalIgnoreCase));
        //            if (dtinfo != null)
        //            {
        //                try
        //                {
        //                    if (val is DataTable)
        //                    {
        //                        par.SqlDbType = SqlDbType.Structured;
        //                        if (dtinfo.Type.ToLower() == "int")
        //                            par.TypeName = "TableType_Int";
        //                        else if (dtinfo.Type.ToLower().StartsWith("nvarchar"))
        //                            par.TypeName = "TableType_Nvarchar";
        //                        else
        //                            par.TypeName = "TableType_varchar";
        //                    }
        //                    else
        //                    {
        //                        par.SqlDbType =
        //                            SqlTypeString2SqlType(dtinfo.Type); //dtinfo.Type.ToEnum<SqlDbType>();
        //                                                                //para.Value = ChanageType(val, par.SqlDbType);
        //                                                                //if (dtinfo.MaxLength > 0)
        //                                                                //    par.Size = dtinfo.MaxLength;
        //                    }
        //                }
        //                catch
        //                {
        //                }
        //            }

        //            #endregion
        //        }
        //        else
        //        {
        //            #region else
        //            try
        //            {
        //                if (val is DataTable dt)
        //                {
        //                    par.SqlDbType = SqlDbType.Structured;
        //                    if (dt.Rows.Count > 0 && dt.Columns[0].DataType == typeof(int))
        //                        par.TypeName = "TableType_Int";
        //                    else if (dt.Rows.Count > 0 && dt.Columns[0].DataType == typeof(string))
        //                        par.TypeName = "TableType_varchar";
        //                    else
        //                        par.TypeName = "TableType_varchar";
        //                }
        //            }
        //            catch
        //            {
        //            }
        //            #endregion
        //        }

        //        par.SqlValue = val;
        //        par.Value = val;
        //    }
        //    #endregion
        //    //此处可能扩展其他数据库类型

        //    return para;
        //}

        //private SqlDbType SqlTypeString2SqlType(string sqlTypeString)
        //{
        //    SqlDbType dbType = SqlDbType.Variant; //默认为Object
        //    sqlTypeString = sqlTypeString.Trim().ToLower();
        //    #region switch (sqlTypeString
        //    //switch (sqlTypeString)
        //    //{
        //    //    case "int":
        //    //        dbType = SqlDbType.Int;
        //    //        break;
        //    //    case "varchar":
        //    //        dbType = SqlDbType.VarChar;
        //    //        break;
        //    //    case "bit":
        //    //        dbType = SqlDbType.Bit;
        //    //        break;
        //    //    case "datetime":
        //    //        dbType = SqlDbType.DateTime;
        //    //        break;
        //    //    case "date":
        //    //        dbType = SqlDbType.Date;
        //    //        break;
        //    //    case "time":
        //    //        dbType = SqlDbType.Time;
        //    //        break;
        //    //    case "datetime2":
        //    //        dbType = SqlDbType.DateTime2;
        //    //        break;
        //    //    case "datetimeoffset":
        //    //        dbType = SqlDbType.DateTimeOffset;
        //    //        break;
        //    //    case "decimal":
        //    //        dbType = SqlDbType.Decimal;
        //    //        break;
        //    //    case "udt":
        //    //        dbType = SqlDbType.Udt;
        //    //        break;
        //    //    case "float":
        //    //        dbType = SqlDbType.Float;
        //    //        break;
        //    //    case "image":
        //    //        dbType = SqlDbType.Image;
        //    //        break;
        //    //    case "money":
        //    //        dbType = SqlDbType.Money;
        //    //        break;
        //    //    case "ntext":
        //    //        dbType = SqlDbType.NText;
        //    //        break;
        //    //    case "nvarchar":
        //    //        dbType = SqlDbType.NVarChar;
        //    //        break;
        //    //    case "smalldatetime":
        //    //        dbType = SqlDbType.SmallDateTime;
        //    //        break;
        //    //    case "smallint":
        //    //        dbType = SqlDbType.SmallInt;
        //    //        break;
        //    //    case "text":
        //    //        dbType = SqlDbType.Text;
        //    //        break;
        //    //    case "bigint":
        //    //        dbType = SqlDbType.BigInt;
        //    //        break;
        //    //    case "binary":
        //    //        dbType = SqlDbType.Binary;
        //    //        break;
        //    //    case "char":
        //    //        dbType = SqlDbType.Char;
        //    //        break;
        //    //    case "nchar":
        //    //        dbType = SqlDbType.NChar;
        //    //        break;
        //    //    case "numeric":
        //    //        dbType = SqlDbType.Decimal;
        //    //        break;
        //    //    case "real":
        //    //        dbType = SqlDbType.Real;
        //    //        break;
        //    //    case "smallmoney":
        //    //        dbType = SqlDbType.SmallMoney;
        //    //        break;
        //    //    case "sql_variant":
        //    //        dbType = SqlDbType.Variant;
        //    //        break;
        //    //    case "timestamp":
        //    //        dbType = SqlDbType.Timestamp;
        //    //        break;
        //    //    case "tinyint":
        //    //        dbType = SqlDbType.TinyInt;
        //    //        break;
        //    //    case "uniqueidentifier":
        //    //        dbType = SqlDbType.UniqueIdentifier;
        //    //        break;
        //    //    case "varbinary":
        //    //        dbType = SqlDbType.VarBinary;
        //    //        break;
        //    //    case "xml":
        //    //        dbType = SqlDbType.Xml;
        //    //        break;
        //    //}
        //    #endregion
        //    var parameterType = GetParameterTypeName(sqlTypeString);
        //    dbType = parameterType.ToEnum<SqlDbType>();
        //    return dbType;
        //}
        #endregion
        #region MappingTypes
        /// <summary>
        /// 类型映射
        /// sqlTypeName，CsharpType，ParameterType
        /// </summary>
        public override List<Tuple<string, string, string>> MappingTypes
        {
            get
            {
                return new List<Tuple<string, string, string>>()
                    {
                        Tuple.Create("bigint", "int", "BigInt"),
                        Tuple.Create("binary", "byte[]", "Binary"),
                        Tuple.Create("bit", "bool", "Bit"),
                        Tuple.Create("char", "string", "Char"),
                        Tuple.Create("datetime", "DateTime", "DateTime"),
                        Tuple.Create("date", "DateTime", "Date"),
                        Tuple.Create("time", "DateTime", "Time"),
                        Tuple.Create("udt", "DateTime", "Udt"),
                        Tuple.Create("datetime2", "DateTime", "DateTime2"),
                        Tuple.Create("datetimeoffset", "DateTime", "DateTimeOffset"),
                        Tuple.Create("decimal", "decimal", "Decimal"),
                        Tuple.Create("float", "decimal", "Float"),
                        Tuple.Create("image", "byte[]", "Image"),
                        Tuple.Create("int", "int", "Int"),
                        Tuple.Create("money", "decimal", "Money"),
                        Tuple.Create("nchar", "string", "NChar"),
                        Tuple.Create("ntext", "string", "NText"),
                        Tuple.Create("numeric", "decimal", "Decimal"),
                        Tuple.Create("nvarchar", "string", "NVarChar"),
                        Tuple.Create("real", "decimal", "Real"),
                        Tuple.Create("smalldatetime", "DateTime", "SmallDateTime"),
                        Tuple.Create("smallint", "int", "SmallInt"),
                        Tuple.Create("smallmoney", "decimal", "SmallMoney"),
                        Tuple.Create("sql_variant", "string", "Variant"),
                        Tuple.Create("text", "string", "Text"),
                        Tuple.Create("timestamp", "string", "Timestamp"),
                        Tuple.Create("tinyint", "int", "TinyInt"),
                        Tuple.Create("uniqueidentifier", "Guid", "UniqueIdentifier"),
                        Tuple.Create("varbinary", "byte[]", "VarBinary"),
                        Tuple.Create("varchar", "string", "VarChar"),
                        Tuple.Create("xml", "string", "Xml"),
                        Tuple.Create("sysname", "string", "Variant")
                };
            }
        }

        /// <summary>
        /// 获取库类型
        /// </summary>
        /// <param name="csharpTypeName"></param>
        /// <returns></returns>
        public override string GetDbTypeName(string csharpTypeName)
        {
            if (csharpTypeName == Constants.ByteArrayType.Name)
                return "varbinary";
            csharpTypeName = CheckCsharpTypeName(csharpTypeName);
            var mappings = this.MappingTypes?.Where(it => it.Item2.Equals(csharpTypeName, StringComparison.CurrentCultureIgnoreCase)).ToList();
            if (mappings != null && mappings.Count > 0)
                return mappings.First().Item1;
            else
                return "varchar";
        }
        /// <summary>
        /// 获取sql Parameter Type
        /// </summary>
        /// <param name="dbTypeName"></param>
        /// <returns></returns>
        public override string GetParameterTypeName(string dbTypeName)
        {
            var mappings = this.MappingTypes?.Where(it => it.Item1.Equals(dbTypeName, StringComparison.CurrentCultureIgnoreCase));
            return !mappings.IsNullOrEmpty_() ? mappings.First().Item3 : "VarChar";
        }
        #endregion

        #region Sql Fun Templet
        /// <summary>
        /// SQL NVL() 从两个表达式返回一个非 null 值	函数
        /// </summary>
        /// <param name="check_expression">将被检查是否为 NULL的表达式。可以是任何类型的。</param>
        /// <param name="replacement_value">在 check_expression 为 NULL时将返回的表达式。replacement_value 必须与 check_expresssion 具有相同的类型。</param>
        /// <returns>如果 check_expression 不为 NULL，那么返回该表达式的值；否则返回 replacement_value</returns>
        public override string SQL_NVL(string check_expression, string replacement_value)
        {
            return $"ISNULL({check_expression},{replacement_value})";
        }
        /// <summary>
        /// 获取数据库时间函数
        /// </summary>
        public override string SqlDateNow
        {
            get
            {
                return " GETDATE() ";
            }
        }
        /// <summary>
        /// 查寻当前时间字符串
        /// </summary>
        public override string FullSqlDateNow
        {
            get
            {
                return "SELECT GETDATE()";
            }
        }

        /// <summary>
        /// SQL UCASE() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>函数把字段的值转换为大写</returns>
        public override string SQL_UCASE(string column_name)
        {
            return $"UPPER({column_name})";
        }

        /// <summary>
        /// SQL LCASE() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>函数把字段的值转换为小写</returns>
        public override string SQL_LCASE(string column_name)
        {
            return $"LOWER({column_name})";
        }

        /// <summary>
        /// SQL LEFT(s,n) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>返回字符串 str 的前 length 个字符</returns>
        public override string SQL_LEFT(string str, int length)
        {
            return $"LEFT({str},{length})";
        }
        /// <summary>
        /// SQL RIGHT(s,n) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>返回字符串 str 的前 length 个字符</returns>
        public override string SQL_RIGHT(string str, int length)
        {
            return $"RIGHT({str},{length})";
        }
        /// <summary>
        /// SQL SUBSTR(s, start, length) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>从字符串 str 的 start 位置截取长度为 length 的子字符串</returns>
        public override string SQL_SUBSTR(string str, int start, int length)
        {
            return $"SUBSTRING({str},{1 + start},{length})";
        }
        /// <summary>
        /// SQL TRIM(s) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>去掉字符串 str 开始和结尾处的空格</returns>
        public override string SQL_TRIM(string str)
        {
            return $"TRIM({str})";
        }
        /// <summary>
        /// SQL CAST() 转换数据类型	函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <param name="type">数据类型</param>
        /// <returns>转换数据类型</returns>
        public override string SQL_CAST(string column_name, string type)
        {
            return $"CAST({column_name} AS {type})";
        }

        public override string SQL_ToInt32(string column_name)
        {
            return SQL_CAST(column_name, "INT");
        }

        public override string SQL_ToInt64(string column_name)
        {
            return SQL_CAST(column_name, "BIGINT");
        }
        public override string SQL_ToDouble(string column_name)
        {
            return SQL_CAST(column_name, "FLOAT");
        }
        /// <summary>转换高精度的十进制数（一般用于货币）</summary>
        /// <param name="column_name"></param>
        /// <param name="d">小数位数</param>
        /// <returns></returns>
        public override string SQL_ToDecimal(string column_name, int d)
        {
            if (d < 0 || d > 10) d = 4;
            return SQL_CAST(column_name, "DECIMAL(18, " + d + ")");
        }

        public override string SQL_ToBool(string column_name)
        {
            return SQL_CAST(column_name, "BIT");
        }

        public override string SQL_ToVarchar(string column_name)
        {
            return SQL_CAST(column_name, "VARCHAR(MAX)");
        }

        public override string SQL_ToGuid(string column_name)
        {
            return SQL_CAST(column_name, "UNIQUEIDENTIFIER");
        }

        public override string SQL_ToDate(string column_name)
        {
            return SQL_CAST(column_name, "DATETIME");
        }

        public override string SQL_ToDateShort(string column_name)
        {
            return SQL_CAST(column_name, "DATE");
        }

        public override string SQL_ToTime(string column_name)
        {
            return SQL_CAST(column_name, "TIME");
        }
        #endregion

        #region SqlTemplet
        /// <summary>
        /// 一般查询语句模板
        /// </summary>
        /// <param name="tbname"></param>
        /// <param name="fieldsstr"></param>
        /// <param name="orderbyStr"></param>
        /// <param name="top"></param>
        /// <returns></returns>
        public override string GetSelectSQL(String tbname, string fieldsstr, string orderbyStr, int top = 0)
        {
            var sqlMeta = @"SELECT " + (top > 0 ? " TOP " + top + " " : "") + fieldsstr + " FROM " + tbname + " WHERE {0} " + orderbyStr;
            return sqlMeta;
        }
        /// <summary>
        /// 分页查询语句模板
        /// </summary>
        /// <param name="tbName">表名</param>
        /// <param name="fieldsstr"></param>
        /// <param name="whereStr"></param>
        /// <param name="orderbyStr"></param>
        /// <param name="page">当前页面</param>
        /// <param name="nums">每页记录数</param>
        /// <returns></returns>
        public override string GetSelectPageSQL(string tbName, string fieldsstr, string whereStr, string orderbyStr, int page = 1, int nums = 25)
        {
            if (page < 1) page = 1;
            if (nums < 1) nums = 10;
            //var m = (page - 1) * nums + 1;
            var n = page * nums;
            var orderbyStr2 = orderbyStr.Replace("ORDER BY", "").Replace(" ASC", "").Replace(" DESC", "").Replace(" asc", "").Replace(" desc", "");
            var sqlMeta = $@"SELECT * FROM(
                    SELECT TOP {nums} * FROM (
                      SELECT TOP {n} {fieldsstr } FROM {tbName}  where {whereStr }
                        )t ORDER BY {orderbyStr2} ASC
                    )t2 ORDER BY {orderbyStr2} DESC ";
            //var sqlMeta = "select * from("
            //                           + "select " + fieldsstr + $" ,ROW_NUMBER() over({orderbyStr}) as RN  from {tbName} where {whereStr }) as t "
            //                        + $" where t.RN between {m} and {n}";
            return sqlMeta;
        }
        /// <summary>
        /// MERGE 合并语句模板
        /// </summary>
        /// <param name="_dbType"></param>
        /// <param name="tbname"></param>
        /// <returns></returns>
        public override string GetMergeSql(String tbname)
        {
            var sqlMeta = @"MERGE " + tbname + " AS [target]  USING(SELECT 1 AS ____Id_____) AS source ON {0}  WHEN MATCHED THEN {1}  WHEN NOT MATCHED THEN {2};";

            return sqlMeta;
        }

        /// <summary>
        /// 更新语句模板
        /// "UPDATE " + (isMerge ? "" : fromStr) + " SET {0} " + (isMerge ? "" : " WHERE {1} ")
        /// </summary>
        /// <param name="_dbType"></param>
        /// <param name="fromStr"></param>
        /// <param name="isMerge">是参与MERGE</param>
        /// <returns></returns>
        public override string GetUpdateSql(String fromStr, bool isMerge = false)
        {
            var sqlMeta = "UPDATE " + (isMerge ? "" : fromStr) + " SET {0} " + (isMerge ? "" : " WHERE {1} ");

            return sqlMeta;
        }
        /// <summary>
        /// 插入语句模板
        /// "INSERT " + (isMerge ? "" : " INTO " + fromStr) + " ({0}) VALUES ({1})"
        /// </summary>
        /// <param name="_dbType"></param>
        /// <param name="fromStr"></param>
        /// <param name="isMerge">是参与MERGE</param>
        /// <returns></returns>
        public override string GetInsertSql(String fromStr, bool isMerge = false)
        {
            var sqlMeta = "INSERT " + (isMerge ? "" : " INTO " + fromStr) + " ({0}) VALUES ({1})";

            return sqlMeta;
        }
        /// <summary>
        /// 统计数量语句模板
        /// $"SELECT Count(0) FROM {fromStr} WHERE {0}";
        /// </summary>
        /// <param name="_dbType"></param>
        /// <param name="fromStr"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public override string GetCountSql(String fromStr, string fieldName = "")
        {
            var sqlMeta = "SELECT Count(" + (fieldName.IsNullOrEmpty() ? "0" : fieldName) + ") FROM " + fromStr + " WHERE {0}";

            return sqlMeta;
        }
        /// <summary>
        /// GetInsertSelectSql
        /// INSERT INTO " + insertTable + " ({0}) " + " SELECT {1} FROM " + selectTable + "  WHERE {2} 
        /// </summary>
        /// <param name="insertTable"></param>
        /// <param name="selectTable"></param>
        /// <param name="_dbType"></param>
        /// <returns></returns>
        public override string GetInsertSelectSql(String insertTable, String selectTable)
        {
            var sqlMeta = "INSERT INTO " + insertTable + " ({0}) " +
                            " SELECT {1} FROM " + selectTable + "  WHERE {2} ";

            return sqlMeta;
        }
        #endregion

        #region WithNextSequence
        /// <summary>
        /// 下个序列脚本
        /// </summary>
        /// <param name="sequenceName"></param>
        /// <returns></returns>
        public override string WithNextSequence(string sequenceName = null)
        {
            if (string.IsNullOrEmpty(sequenceName))
            {
                sequenceName = "default_seq";
            }
            return $"nextval('{sequenceName}')";
        }
        #endregion

        #region DML CodeFirst
        /// <summary>
        /// 获取所有数据库
        /// </summary>
        public override string GetDataBaseSql
        {
            get
            {
                return "SELECT Name FROM MASTER.DBO.SYSDATABASES ORDER BY Name";
            }
        }
        /// <summary>
        /// 获取数据库中的所有表
        /// </summary>
        public override string GetTableInfoListSql
        {
            get
            {
                return "SELECT name,xtype,'' AS [Description] From {0}.dbo.sysobjects WHERE xtype = 'u'  ORDER BY name";
            }
        }
        /// <summary>
        /// 获取数据库中的所有视图
        /// </summary>
        public override string GetViewInfoListSql
        {
            get
            {
                return "SELECT name,xtype,'' AS [Description] From {0}.dbo.sysobjects WHERE xtype = 'v'  ORDER BY name";
            }
        }
        /// <summary>
        /// 得到一个表的所有列信息
        /// </summary>
        public override string GetColumnInfosByTableNameSql
        {
            get
            {
                return @"SELECT
                        ORDINAL_POSITION  AS [ColumnId],
                        COLUMN_NAME AS [Name],
                        DATA_TYPE AS [Type],
                        [IsIdentity] = CONVERT(BIT, ( Select count(0) from sysobjects so
                                   Where so.xtype = 'U'
                                     and upper(so.name) = upper(c.TABLE_NAME)
                                     and Exists(Select Top 1 1 from syscolumns sc
                                                 Where sc.id = so.id and sc.name=c.COLUMN_NAME
                                                   and columnproperty(sc.id, sc.name, 'IsIdentity') = 1
                                               ))),
                        [IsNullable] = CONVERT(BIT,case when IS_NULLABLE ='yes' then 1 else 0 end),
                        [IsPrimaryKey] = CONVERT(BIT, (SELECT  count(0) FROM {0}.INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME=c.TABLE_NAME and COLUMN_NAME=c.COLUMN_NAME )),
                        [MaxLength]=isnull(CHARACTER_MAXIMUM_LENGTH,0),
                        [value] AS [Description]
                        FROM {0}.INFORMATION_SCHEMA.COLUMNS AS C
                        left JOIN ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '{1}','column', default) AS E
                        ON C.COLUMN_NAME = E.objname
                        WHERE TABLE_NAME = '{1}'
                        order by ORDINAL_POSITION";
            }
        }
        #endregion

        #region DDL
        public override IDbFirst DbFirst { get { return new SqlServerDbFirst(this.dbHelper.DBName); } }
        #region Scattered
        protected override string CreateTableNull
        {
            get
            {
                return "NULL";
            }
        }
        protected override string CreateTableNotNull
        {
            get
            {
                return "NOT NULL";
            }
        }
        protected override string CreateTablePirmaryKey
        {
            get
            {
                return "PRIMARY KEY";
            }
        }
        protected override string CreateTableIdentity
        {
            get
            {
                return "IDENTITY(1,1)";
            }
        }

        #endregion
        #region DDL SQL
        protected override string CreateDataBaseSql
        {
            get
            {
                return @"create database {0}  ";
            }
        }
        protected override string AddPrimaryKeySql
        {
            get
            {
                return "ALTER TABLE {0} ADD CONSTRAINT {1} PRIMARY KEY({2})";
            }
        }
        protected override string AddColumnToTableSql
        {
            get
            {
                return "ALTER TABLE {0} ADD {1} {2}{3} {4} {5} {6}";
            }
        }
        protected override string AlterColumnToTableSql
        {
            get
            {
                return "ALTER TABLE {0} ALTER COLUMN {1} {2}{3} {4} {5} {6}";
            }
        }
        protected override string BackupDataBaseSql
        {
            get
            {
                return @"USE master;BACKUP DATABASE {0} TO disk = '{1}'";
            }
        }
        protected override string CreateTableSql
        {
            get
            {
                return "CREATE TABLE {0}(\r\n{1})";
            }
        }
        protected override string CreateTableColumn
        {
            get
            {
                return "{0} {1}{2} {3} {4} {5}";
            }
        }
        protected override string TruncateTableSql
        {
            get
            {
                return "TRUNCATE TABLE {0}";
            }
        }
        protected override string BackupTableSql
        {
            get
            {
                return "SELECT TOP {0} * INTO {1} FROM  {2}";
            }
        }
        protected override string DropTableSql
        {
            get
            {
                return "DROP TABLE {0}";
            }
        }
        protected override string DropColumnToTableSql
        {
            get
            {
                return "ALTER TABLE {0} DROP COLUMN {1}";
            }
        }
        protected override string DropConstraintSql
        {
            get
            {
                return "ALTER TABLE {0} DROP CONSTRAINT  {1}";
            }
        }
        protected override string RenameColumnSql
        {
            get
            {
                return "exec sp_rename '{0}.{1}','{2}','column';";
            }
        }
        protected override string AddColumnRemarkSql
        {
            get
            {
                return "EXECUTE sp_addextendedproperty N'MS_Description', '{2}', N'user', N'dbo', N'table', N'{1}', N'column', N'{0}'"; ;
            }
        }

        protected override string DeleteColumnRemarkSql
        {
            get
            {
                return "EXEC sp_dropextendedproperty 'MS_Description','user',dbo,'table','{1}','column','{0}'";
            }

        }

        protected override string IsAnyColumnRemarkSql
        {
            get
            {
                return @"SELECT" +
                                " A.name AS table_name," +
                                " B.name AS column_name," +
                                " C.value AS column_description" +
                                " FROM sys.tables A" +
                                " LEFT JOIN sys.extended_properties C ON C.major_id = A.object_id" +
                                " LEFT JOIN sys.columns B ON B.object_id = A.object_id AND C.minor_id = B.column_id" +
                                " INNER JOIN sys.schemas SC ON SC.schema_id = A.schema_id AND SC.name = 'dbo'" +
                                " WHERE A.name = '{1}' and b.name = '{0}'";

            }
        }

        protected override string AddTableRemarkSql
        {
            get
            {
                return "EXECUTE sp_addextendedproperty N'MS_Description', '{1}', N'user', N'dbo', N'table', N'{0}', NULL, NULL";
            }
        }

        protected override string DeleteTableRemarkSql
        {
            get
            {
                return "EXEC sp_dropextendedproperty 'MS_Description','user',dbo,'table','{0}' ";
            }

        }

        protected override string IsAnyTableRemarkSql
        {
            get
            {
                return @"SELECT C.class_desc
                                FROM sys.tables A 
                                LEFT JOIN sys.extended_properties C ON C.major_id = A.object_id 
								INNER JOIN sys.schemas SC ON  SC.schema_id=A.schema_id AND SC.name='dbo'
                                WHERE A.name = '{0}'  AND minor_id=0";
            }

        }

        protected override string RenameTableSql
        {
            get
            {
                return "EXEC sp_rename '{0}','{1}'";
            }
        }

        protected override string CreateIndexSql
        {
            get
            {
                return "CREATE {3} NONCLUSTERED INDEX Index_{0}_{2} ON {0}({1})";
            }
        }
        protected override string AddDefaultValueSql
        {
            get
            {
                return "alter table {0} ADD DEFAULT '{2}' FOR {1}";
            }
        }
        protected override string IsAnyIndexSql
        {
            get
            {
                return "select count(*) from sys.indexes where name='{0}'";
            }
        }
        #endregion

        #region Methods

        /// <summary>
        ///by current connection string
        /// </summary>
        /// <param name="databaseDirectory"></param>
        /// <returns></returns>
        public override bool CreateDatabase(string databaseName, string databaseDirectory = null)
        {
            if (databaseDirectory != null)
            {
                if (!FileHelper.IsExistDirectory(databaseDirectory))
                {
                    FileHelper.CreateDirectory(databaseDirectory);
                }
            }
            var newDb = new DbMaintenanceProvider(this.dbHelper.DBName);
            var oldDatabaseName = Connection.Database;
            var connection = this.dbHelper.ConnectString;
            connection = connection.Replace(oldDatabaseName, "master");
            newDb.ConnectString = connection;
            if (!newDb.GetAllDatabases().Any(it => it.Equals(databaseName, StringComparison.CurrentCultureIgnoreCase)))
            {
                var sql = CreateDataBaseSql;
                if (databaseDirectory.HasValue())
                {
                    sql += @"on primary 
                                        (
                                            name = N'{0}',
                                            filename = N'{1}\{0}.mdf',
                                            size = 10mb,
                                            maxsize = 100mb,
                                            filegrowth = 1mb
                                        ),
                                        (
                                            name = N'{0}_ndf',   
                                            filename = N'{1}\{0}.ndf',
                                            size = 10mb,
                                            maxsize = 100mb,
                                             filegrowth = 10 %
                                        )
                                        log on  
                                        (
                                            name = N'{0}_log',
                                            filename = N'{1}\{0}.ldf',
                                            size = 100mb,
                                            maxsize = 1gb,
                                            filegrowth = 10mb
                                        ); ";
                }
                var sqle = newDb.CreateSqlEntity(string.Format(sql, databaseName, databaseDirectory));
                newDb.Execute(sqle);
            }
            return true;
        }
        public override bool CreateTable(string tableName, List<DbColumnInfo> columns, bool isCreatePrimaryKey = true)
        {
            tableName = this.GetTranslationTableName(tableName);
            string sql = GetCreateTableSql(tableName, columns);
            var sqle = this.dbHelper.CreateSqlEntity(sql);
            this.dbHelper.Execute(sqle);
            if (isCreatePrimaryKey)
            {
                var pkColumns = columns.Where(it => it.IsPrimaryKey).ToList();
                if (pkColumns.Count > 1)
                {
                    AddPrimaryKeys(tableName, pkColumns.Select(it => it.Name).ToArray());
                }
                else
                {
                    foreach (var item in pkColumns)
                    {
                        AddPrimaryKey(tableName, item.Name);
                    }
                }
            }
            return true;
        }
        public override List<DbColumnInfo> GetColumnInfosByTableName(string tableName, string dbName, bool isCache = true)
        {
            tableName = GetNoTranslationColumnName(tableName);
            var result = base.GetColumnInfosByTableName(tableName, dbName,isCache);
            return result;
        }
        public override bool RenameColumn(string tableName, string oldColumnName, string newColumnName)
        {
            tableName = this.GetTranslationTableName(tableName);
            oldColumnName = this.GetTranslationColumnName(oldColumnName);
            newColumnName = this.GetNoTranslationColumnName(newColumnName);
            string sql = string.Format(this.RenameColumnSql, tableName, oldColumnName, newColumnName);
            var sqle = this.dbHelper.CreateSqlEntity(sql);
            this.dbHelper.Execute(sqle);
            return true;
        }
        #endregion
        #endregion
    }
}
